import dayjs from "dayjs";
import db from "../db/mysql.js";
import {formatNumber, getPreviousPeriod, roundTo2} from "./utils.js";


/*抖音项目分析总看板 sql  数据 */
export async function getZongKanBanDatas(date_or_month, star_parm, end_parm) {

    const sql_params = [];
    // -- ① 交易汇总
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    // -- 千川成本（3张表 UNION）
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);

    //-- ③ 人工成本
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    if (date_or_month === "date") {
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
    }
    sql_params.push(star_parm);
    sql_params.push(end_parm);

    //-- ④ 分佣金额
    sql_params.push(star_parm);
    sql_params.push(end_parm);

    let zong_kan_ban_sql = ``
    if (date_or_month === "date") {
        zong_kan_ban_sql = `
SELECT 
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(b.qian_chuan_cost, 2) AS qian_chuan_cost,
    ROUND(b.gua_tie_cost, 2) AS gua_tie_cost,
    ROUND((c.ren_gong_cost+(a.yong_hu_zhi_fu_price-a.tui_kuan_price)*0.011), 2)  AS ren_gong_cost,
    ROUND(d.fen_yong_price, 2) AS fen_yong_price,
    
    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,   
    -- ROI
    IF((b.qian_chuan_cost + b.gua_tie_cost) > 0,
       ROUND((a.yong_hu_zhi_fu_price - b.bu_dan_cost) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,
       
    -- 业务利润
    ROUND(d.fen_yong_price - b.qian_chuan_cost - b.gua_tie_cost - (c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 2) AS ye_wu_li_run,
    
    -- 利润率
    ROUND(
      (d.fen_yong_price - b.qian_chuan_cost - b.gua_tie_cost -(c.ren_gong_cost+(a.yong_hu_zhi_fu_price-a.tui_kuan_price)*0.011))
      * 100 / a.yong_hu_zhi_fu_price, 2
    ) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE date BETWEEN ? AND  ?
      AND shop_name IS NOT NULL
) AS a

CROSS JOIN
(
    -- 千川成本（3张表 UNION）
    SELECT

    (
        SELECT ROUND(SUM(qc.all_cost), 2)
        FROM (
            SELECT dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE date BETWEEN ? AND  ?

            UNION ALL

            SELECT dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE date BETWEEN ? AND  ?

            UNION ALL

            SELECT dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE date BETWEEN ? AND  ?
        ) AS qc
    ) AS qian_chuan_cost,

    -- 自播成本：补单费用
    (
        SELECT SUM(order_subsidy)
        FROM dd_self_live_summary
        WHERE date BETWEEN ? AND  ?
          AND shop_name IS NOT NULL
    ) AS bu_dan_cost,

    -- 自播成本：挂贴费用
    (
        SELECT SUM(hanging_fee)
        FROM dd_self_live_summary
        WHERE date BETWEEN ? AND  ?
          AND shop_name IS NOT NULL
    ) AS gua_tie_cost

) AS b

CROSS JOIN
(
    -- ③ 人工成本
    SELECT 
    (
        -- ① 直播人工成本
        IFNULL((SELECT SUM(day_cost)
                FROM dd_day_live_cheng_ben
                WHERE date BETWEEN ? AND ? ), 0)
        +

        -- ② 兼职人工成本
        IFNULL((SELECT SUM(salary_day)
                FROM dd_employee_salary_jian_zhi_base
                WHERE date BETWEEN ? AND ? ), 0)
        +

        -- ③ 公共工资分摊成本
        IFNULL((
            SELECT SUM(total_cost)
            FROM (
                SELECT 
                    t.month_str,
                    t.avg_day_live_room_cost,

                    (DATEDIFF(
                        LEAST(t.last_day, ? ),
                        GREATEST(t.first_day, ? )
                    ) + 1) AS cover_days,

                    ROUND(
                        (DATEDIFF(
                            LEAST(t.last_day, ? ),
                            GREATEST(t.first_day, ? )
                        ) + 1) * t.avg_day_live_room_cost,
                    2) AS total_cost
                    
                FROM (
                    SELECT 
                        month_str,
                        ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_live_room_cost,
                        STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d') AS first_day,
                        LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d')) AS last_day
                    FROM dd_employee_salary_pubilc_base
                    GROUP BY month_str
                ) t
                WHERE t.last_day >= ?
                  AND t.first_day <= ?
            ) AS x
        ), 0)
    ) 
    AS ren_gong_cost
    
    
   
) AS c

CROSS JOIN
(
    -- ④ 分佣金额
    SELECT 
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary
        WHERE date BETWEEN ? AND  ?
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
) AS d LIMIT 1;
`;

    } else {
        zong_kan_ban_sql = `
    SELECT 
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(b.qian_chuan_cost, 2) AS qian_chuan_cost,
    ROUND(b.gua_tie_cost, 2) AS gua_tie_cost,
    ROUND(c.ren_gong_cost+(a.yong_hu_zhi_fu_price-a.tui_kuan_price)*0.011, 2) AS ren_gong_cost,
    ROUND(d.fen_yong_price, 2) AS fen_yong_price,
    
    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,
       
    -- ROI
    IF((b.qian_chuan_cost + b.gua_tie_cost) > 0,
       ROUND((a.yong_hu_zhi_fu_price - b.bu_dan_cost) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,
       
    -- 业务利润
    ROUND((d.fen_yong_price - b.qian_chuan_cost - b.gua_tie_cost - 
    (c.ren_gong_cost+(a.yong_hu_zhi_fu_price-a.tui_kuan_price)*0.011)), 2) AS ye_wu_li_run,
    
    -- 利润率
    ROUND(
      (d.fen_yong_price - b.qian_chuan_cost - b.gua_tie_cost - 
      (c.ren_gong_cost+(a.yong_hu_zhi_fu_price-a.tui_kuan_price)*0.011))
      * 100 / a.yong_hu_zhi_fu_price, 2
    ) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE month_str BETWEEN ? AND  ?
      AND shop_name IS NOT NULL
) AS a

CROSS JOIN
(
    -- 千川成本（3张表 UNION）
   SELECT
    (
        SELECT ROUND(SUM(qc.all_cost), 2)
        FROM (
            SELECT dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE \`year_month\` BETWEEN ? AND  ?

            UNION ALL

            SELECT dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE \`year_month\` BETWEEN ? AND  ?

            UNION ALL

            SELECT dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE \`year_month\` BETWEEN ? AND  ?
        ) AS qc
    ) AS qian_chuan_cost,

    -- 自播成本：补单费用
    (
        SELECT SUM(order_subsidy)
        FROM dd_self_live_summary
        WHERE \`month\` BETWEEN ? AND  ?
          AND shop_name IS NOT NULL
    ) AS bu_dan_cost,

    -- 自播成本：挂贴费用
    (
        SELECT SUM(hanging_fee)
        FROM dd_self_live_summary
        WHERE \`month\` BETWEEN ? AND  ?
          AND shop_name IS NOT NULL
    ) AS gua_tie_cost
) AS b

CROSS JOIN
(
    -- ③ 人工成本    
    SELECT 
(
    -- ① 直播人工成本（按月）
    IFNULL((
        SELECT SUM(day_cost)
        FROM dd_day_live_cheng_ben
        WHERE LEFT(date, 7) BETWEEN ? AND ?
    ), 0)
    +
    -- ② 兼职人工成本（按月）
    IFNULL((
        SELECT SUM(salary_day)
        FROM dd_employee_salary_jian_zhi_base
        WHERE LEFT(date, 7) BETWEEN ? AND ?
    ), 0)
    +
    -- ③ 公共工资（整月成本 = avg_day_cost × 当月天数）
    IFNULL((
        SELECT SUM(
            ROUND(
                avg_day_cost * month_days,
                2
            )
        )
        FROM (
            SELECT 
                month_str,
                ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_cost,
                DAY(LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d'))) AS month_days
            FROM dd_employee_salary_pubilc_base
            WHERE month_str BETWEEN ? AND ?
            GROUP BY month_str
        ) AS t
    ), 0)
) AS ren_gong_cost
    
) AS c

CROSS JOIN
(
    -- ④ 分佣金额
    SELECT 
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary
        WHERE month_str BETWEEN ? AND  ?
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
) AS d LIMIT 1;`;
    }

    const zong_kan_ban_datas = await db.query(zong_kan_ban_sql, sql_params);
    //size 是前端的卡片的占比
    let zong_kan_ban_datas_json = {
        "yong_hu_zhi_fu_price": {id: 1, title: "支付金额", value: "0", size: 1},
        "tui_kuan_price": {id: 2, title: "退款金额", value: "0", size: 1},
        "qian_shou_lv": {id: 3, title: "签收率", value: "0", size: 0.5},
        "qian_chuan_cost": {id: 4, title: "千川消耗", value: "0", size: 1},
        "roi": {id: 5, title: "ROI", value: "0", size: 0.5},
        "ren_gong_cost": {id: 6, title: "人工成本", value: "0", size: 1},
        "ye_wu_li_run": {id: 7, title: "业务利润", value: "0", size: 1},
        "li_run_lv": {id: 8, title: "利润率", value: "0", size: 0.5},
    }

    const zong_kan_ban_datas_res = [];

    if (zong_kan_ban_datas != null && zong_kan_ban_datas.length > 0) {
        const zong_kan_ban_datas_child = zong_kan_ban_datas[0]
        for (const key in zong_kan_ban_datas_child) {
            if (key === "li_run_lv" || key === "qian_shou_lv") {

                const value_res = `${zong_kan_ban_datas_child[key]}%`

                if (zong_kan_ban_datas_child[key] != null) {
                    zong_kan_ban_datas_json[key]["value"] = `${zong_kan_ban_datas_child[key]}%`
                } else {
                    zong_kan_ban_datas_json[key]["value"] = `0%`
                }
                zong_kan_ban_datas_res.push(zong_kan_ban_datas_json[key])

            } else if (key === "gua_tie_cost" || key === "fen_yong_price") {
                //挂铁和分佣没有 zong_kan_ban_datas_json 没有key
            } else {
                const value_res = await formatNumber(zong_kan_ban_datas_child[key])
                zong_kan_ban_datas_json[key]["value"] = value_res
                zong_kan_ban_datas_res.push(zong_kan_ban_datas_json[key])
            }

        }
    }

    return zong_kan_ban_datas_res;
}


/*整体成交_折线图  sql + datas*/
export async function getZhengTiChengJiaoLineDatasRes(date_or_month, star_parm, end_parm, allDates) {
    const sql_params = [];
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);

    let zheng_ti_cheng_jiao_line_sql = ``
    if (date_or_month === "date") {
        zheng_ti_cheng_jiao_line_sql = `
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE date BETWEEN ? AND  ?
    GROUP BY date
) AS a
LEFT JOIN (
    SELECT date,ROUND(SUM(all_cost),2) AS qian_chuan_cost
    FROM (
        SELECT date,all_cost FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE date BETWEEN ? AND  ?
        UNION ALL
        SELECT date,all_cost FROM dd_qian_chuan_quanyu_plan
        WHERE date BETWEEN ? AND  ?
        UNION ALL
        SELECT date,all_cost FROM dd_qian_chuan_quanyu_touhou
        WHERE date BETWEEN ? AND  ?
        ) AS t  GROUP BY t.date
) AS b
ON a.date = b.date
UNION
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE date  BETWEEN ? AND  ?
    GROUP BY date
) AS a
RIGHT JOIN (
    SELECT date,ROUND(SUM(all_cost),2) AS qian_chuan_cost
    FROM (
        SELECT date,all_cost FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE date BETWEEN ? AND  ?
        UNION ALL
        SELECT date,all_cost FROM dd_qian_chuan_quanyu_plan
        WHERE date BETWEEN ? AND  ?
        UNION ALL
        SELECT date,all_cost FROM dd_qian_chuan_quanyu_touhou
        WHERE date BETWEEN ? AND  ?
        ) AS t  GROUP BY t.date
) AS b
ON a.date = b.date

`;
    } else {
        zheng_ti_cheng_jiao_line_sql = `
        SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE month_str BETWEEN ? AND  ? 

    GROUP BY date
) AS a
LEFT JOIN (
SELECT date,ROUND(SUM(all_cost),2) AS qian_chuan_cost
FROM (
    SELECT date,all_cost FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE \`year_month\` BETWEEN ? AND  ?
    UNION ALL
    SELECT date,all_cost FROM dd_qian_chuan_quanyu_plan
        WHERE \`year_month\` BETWEEN ? AND  ?
    UNION ALL
    SELECT date,all_cost FROM dd_qian_chuan_quanyu_touhou
        WHERE \`year_month\` BETWEEN ? AND  ?
) AS t  GROUP BY t.date
) AS b
ON a.date = b.date
UNION
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE month_str  BETWEEN ? AND  ? 

    GROUP BY date
) AS a
RIGHT JOIN (
    SELECT date,ROUND(SUM(all_cost),2) AS qian_chuan_cost
    FROM (
        SELECT date,all_cost FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE \`year_month\` BETWEEN ? AND  ?
        UNION ALL
        SELECT date,all_cost FROM dd_qian_chuan_quanyu_plan
            WHERE \`year_month\` BETWEEN ? AND  ?
        UNION ALL
        SELECT date,all_cost FROM dd_qian_chuan_quanyu_touhou
            WHERE \`year_month\` BETWEEN ? AND  ?
    ) AS t  GROUP BY t.date
) AS b
ON a.date = b.date;
        `
    }


    const zheng_ti_cheng_jiao_line_datas = await db.query(zheng_ti_cheng_jiao_line_sql, sql_params);

    let zheng_ti_cheng_jiao_line_datas_res = {}
    zheng_ti_cheng_jiao_line_datas_res["days"] = []
    zheng_ti_cheng_jiao_line_datas_res["pay"] = []
    zheng_ti_cheng_jiao_line_datas_res["refund"] = []
    zheng_ti_cheng_jiao_line_datas_res["discount"] = []


    let zheng_ti_cheng_jiao_line_datas_json = {}
    for (const item of zheng_ti_cheng_jiao_line_datas) {
        const date_str = item["date_str"];
        zheng_ti_cheng_jiao_line_datas_json[date_str] = item
        const yong_hu_zhi_fu_price = item["yong_hu_zhi_fu_price"];
        const tui_kuan_price = item["tui_kuan_price"];
        const qian_chuan_cost = item["qian_chuan_cost"];
    }

    for (const date_str of allDates) {
        zheng_ti_cheng_jiao_line_datas_res["days"].push(date_str);
        if (date_str in zheng_ti_cheng_jiao_line_datas_json) {
            const item = zheng_ti_cheng_jiao_line_datas_json[date_str]
            const yong_hu_zhi_fu_price = item["yong_hu_zhi_fu_price"];
            zheng_ti_cheng_jiao_line_datas_res["pay"].push(yong_hu_zhi_fu_price);
            const tui_kuan_price = item["tui_kuan_price"];
            zheng_ti_cheng_jiao_line_datas_res["refund"].push(tui_kuan_price);
            const qian_chuan_cost = item["qian_chuan_cost"];
            zheng_ti_cheng_jiao_line_datas_res["discount"].push(qian_chuan_cost);
        }
    }

    return zheng_ti_cheng_jiao_line_datas_res;

}


/*整体成交_每个店铺指标数据 sql + datas */
export async function getShopDatas(date_or_month, star_parm, end_parm, allDates) {

    const sql_params = [];
    const sql_params_star_end_parm = [];
    sql_params_star_end_parm.push(star_parm);
    sql_params_star_end_parm.push(end_parm);
    //-- ① 交易汇总
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    //-- ② 千川成本
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    //-- ③ 人工成本
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    if (date_or_month === "date"){
        sql_params.push(end_parm);
        sql_params.push(star_parm);
    }
    sql_params.push(star_parm);
    sql_params.push(end_parm);
    //-- ④ 分佣金额
    sql_params.push(star_parm);
    sql_params.push(end_parm);

    let shop_kan_ban_sql = ``
    let shop_kan_ban_sql_self = ``
    let shop_kan_ban_sql_coop = ``
    if (date_or_month === "date") {
        shop_kan_ban_sql = `
    SELECT 
    a.shop_name,
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(IFNULL(b.qian_chuan_cost, 0), 2) AS qian_chuan_cost,
    ROUND(IFNULL(b.gua_tie_cost, 0), 2) AS gua_tie_cost,
    ROUND(IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 0), 2) AS ren_gong_cost,
    ROUND(IFNULL(d.fen_yong_price, 0), 2) AS fen_yong_price,
    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,

    -- ROI
    IF((IFNULL(b.qian_chuan_cost,0) + IFNULL(b.gua_tie_cost,0)) > 0,
       ROUND((a.yong_hu_zhi_fu_price - IFNULL(b.bu_dan_cost,0)) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,

    -- 业务利润
    ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
    IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0)), 2) AS ye_wu_li_run,

    -- 利润率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
       IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0))
       * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        shop_name,
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount))  AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE date BETWEEN ? AND ? 
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS a

LEFT JOIN
(
     -- ② 千川成本
SELECT 
    all_shop.shop_name,
    IFNULL(q.qian_chuan_cost, 0) AS qian_chuan_cost,
    IFNULL(s.bu_dan_cost, 0) AS bu_dan_cost,
    IFNULL(s.gua_tie_cost, 0) AS gua_tie_cost
FROM 
(
    -- 店铺全集（去重）
    SELECT DISTINCT shop_name FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_pu_tong_zheng_ti
    WHERE date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_plan
    WHERE date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_touhou
    WHERE date BETWEEN ? AND ?
) AS all_shop

LEFT JOIN 
(
    -- 自播：补单费 / 挂贴费
    SELECT 
        shop_name,
        SUM(order_subsidy) AS bu_dan_cost,
        SUM(hanging_fee) AS gua_tie_cost
    FROM dd_self_live_summary
    WHERE date BETWEEN ? AND ?
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS s
    ON all_shop.shop_name = s.shop_name

LEFT JOIN
(
    -- 千川成本（3表合并）
    SELECT 
        dou_dian AS shop_name,
        SUM(all_cost) AS qian_chuan_cost
    FROM (
        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE date BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_plan
        WHERE date BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_touhou
        WHERE date BETWEEN ? AND ?
    ) AS qc
    GROUP BY dou_dian
) AS q
    ON all_shop.shop_name = q.shop_name

ORDER BY all_shop.shop_name

) AS b ON a.shop_name = b.shop_name

LEFT JOIN
(
    -- ③ 人工成本    
    SELECT 
    shop_name,
    SUM(cost) AS ren_gong_cost
FROM (

    /* ① 直播人工成本（按店铺） */
    SELECT 
        shop_name,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE date BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ② 兼职人工成本（按店铺） */
    SELECT 
        shop_name,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE date BETWEEN  ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ③ 公共工资分摊成本（按店铺） */
    SELECT 
        shop_name,
        SUM(
            ROUND(
                (DATEDIFF(
                    LEAST(t.last_day, ? ),
                    GREATEST(t.first_day, ? )
                ) + 1) * t.avg_day_live_room_cost
            , 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            shop_name,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_live_room_cost,
            STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d') AS first_day,
            LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d')) AS last_day
        FROM dd_employee_salary_pubilc_base
        GROUP BY month_str, shop_name
    ) t
    WHERE t.last_day >= ?
      AND t.first_day <= ?
    GROUP BY shop_name

) AS merged
GROUP BY shop_name

    
    
) AS c ON a.shop_name = c.shop_name

LEFT JOIN
(
    -- ④ 分佣金额
    SELECT 
        t.shop_name,
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary
        WHERE date BETWEEN ? AND ? 
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
    GROUP BY t.shop_name
) AS d ON a.shop_name = d.shop_name

ORDER BY a.yong_hu_zhi_fu_price DESC;
`

        shop_kan_ban_sql_self = `
        SELECT 
    a.shop_name,
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(IFNULL(b.qian_chuan_cost, 0), 2) AS qian_chuan_cost,
    ROUND(IFNULL(b.gua_tie_cost, 0), 2) AS gua_tie_cost,
    ROUND(IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 0), 2) AS ren_gong_cost,
    ROUND(IFNULL(d.fen_yong_price, 0), 2) AS fen_yong_price,

    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,

    -- ROI
    IF((IFNULL(b.qian_chuan_cost,0) + IFNULL(b.gua_tie_cost,0)) > 0,
       ROUND((a.yong_hu_zhi_fu_price - IFNULL(b.bu_dan_cost,0)) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,

    -- 业务利润
    ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
    IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0)), 2) AS ye_wu_li_run,

    -- 利润率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
       IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0))
       * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        shop_name,
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary_self
    WHERE date BETWEEN ? AND ? 
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS a

LEFT JOIN
(
    -- ② 千川成本
    SELECT 
    all_shop.shop_name,
    IFNULL(q.qian_chuan_cost, 0) AS qian_chuan_cost,
    IFNULL(s.bu_dan_cost, 0) AS bu_dan_cost,
    IFNULL(s.gua_tie_cost, 0) AS gua_tie_cost
FROM 
(
    -- 店铺全集（去重）
    SELECT DISTINCT shop_name FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_pu_tong_zheng_ti
    WHERE date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_plan
    WHERE date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_touhou
    WHERE date BETWEEN ? AND ?
) AS all_shop

LEFT JOIN 
(
    -- 自播：补单费 / 挂贴费
    SELECT 
        shop_name,
        SUM(order_subsidy) AS bu_dan_cost,
        SUM(hanging_fee) AS gua_tie_cost
    FROM dd_self_live_summary
    WHERE date BETWEEN ? AND ?
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS s
    ON all_shop.shop_name = s.shop_name

LEFT JOIN
(
    -- 千川成本（3表合并）
    SELECT 
        dou_dian AS shop_name,
        SUM(all_cost) AS qian_chuan_cost
    FROM (
        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE date BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_plan
        WHERE date BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_touhou
        WHERE date BETWEEN ? AND ?
    ) AS qc
    GROUP BY dou_dian
) AS q
    ON all_shop.shop_name = q.shop_name

ORDER BY all_shop.shop_name

) AS b ON a.shop_name = b.shop_name

LEFT JOIN
(
       -- ③ 人工成本    
    SELECT 
    shop_name,
    SUM(cost) AS ren_gong_cost
FROM (

    /* ① 直播人工成本（按店铺） */
    SELECT 
        shop_name,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE date BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ② 兼职人工成本（按店铺） */
    SELECT 
        shop_name,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE date BETWEEN  ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ③ 公共工资分摊成本（按店铺） */
    SELECT 
        shop_name,
        SUM(
            ROUND(
                (DATEDIFF(
                    LEAST(t.last_day, ? ),
                    GREATEST(t.first_day, ? )
                ) + 1) * t.avg_day_live_room_cost
            , 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            shop_name,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_live_room_cost,
            STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d') AS first_day,
            LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d')) AS last_day
        FROM dd_employee_salary_pubilc_base
        GROUP BY month_str, shop_name
    ) t
    WHERE t.last_day >= ?
      AND t.first_day <= ?
    GROUP BY shop_name

) AS merged
GROUP BY shop_name
    
) AS c ON a.shop_name = c.shop_name

LEFT JOIN
(
    -- ④ 分佣金额
    SELECT 
        t.shop_name,
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary_self
        WHERE date BETWEEN ? AND ? 
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
    GROUP BY t.shop_name
) AS d ON a.shop_name = d.shop_name

ORDER BY a.yong_hu_zhi_fu_price DESC
        `

        shop_kan_ban_sql_coop = `
        SELECT 
    a.shop_name,
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(IFNULL(b.qian_chuan_cost, 0), 2) AS qian_chuan_cost,
    ROUND(IFNULL(b.gua_tie_cost, 0), 2) AS gua_tie_cost,
    ROUND(IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 0), 2) AS ren_gong_cost,
    ROUND(IFNULL(d.fen_yong_price, 0), 2) AS fen_yong_price,

    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,

    -- ROI
    IF((IFNULL(b.qian_chuan_cost,0) + IFNULL(b.gua_tie_cost,0)) > 0,
       ROUND((a.yong_hu_zhi_fu_price - IFNULL(b.bu_dan_cost,0)) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,

    -- 业务利润
    ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
    IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0)), 2) AS ye_wu_li_run,

    -- 利润率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
       IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0))
       * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        shop_name,
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary_coop
    WHERE date BETWEEN ? AND ? 
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS a

LEFT JOIN
(
    -- ② 千川成本
    SELECT 
    all_shop.shop_name,
    IFNULL(q.qian_chuan_cost, 0) AS qian_chuan_cost,
    IFNULL(s.bu_dan_cost, 0) AS bu_dan_cost,
    IFNULL(s.gua_tie_cost, 0) AS gua_tie_cost
FROM 
(
    -- 店铺全集（去重）
    SELECT DISTINCT shop_name FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_pu_tong_zheng_ti
    WHERE date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_plan
    WHERE date BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_touhou
    WHERE date BETWEEN ? AND ?
) AS all_shop

LEFT JOIN 
(
    -- 自播：补单费 / 挂贴费
    SELECT 
        shop_name,
        SUM(order_subsidy) AS bu_dan_cost,
        SUM(hanging_fee) AS gua_tie_cost
    FROM dd_self_live_summary
    WHERE date BETWEEN ? AND ?
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS s
    ON all_shop.shop_name = s.shop_name

LEFT JOIN
(
    -- 千川成本（3表合并）
    SELECT 
        dou_dian AS shop_name,
        SUM(all_cost) AS qian_chuan_cost
    FROM (
        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE date BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_plan
        WHERE date BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_touhou
        WHERE date BETWEEN ? AND ?
    ) AS qc
    GROUP BY dou_dian
) AS q
    ON all_shop.shop_name = q.shop_name

ORDER BY all_shop.shop_name

) AS b ON a.shop_name = b.shop_name

LEFT JOIN
(
        -- ③ 人工成本    
    SELECT 
    shop_name,
    SUM(cost) AS ren_gong_cost
FROM (

    /* ① 直播人工成本（按店铺） */
    SELECT 
        shop_name,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE date BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ② 兼职人工成本（按店铺） */
    SELECT 
        shop_name,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE date BETWEEN  ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ③ 公共工资分摊成本（按店铺） */
    SELECT 
        shop_name,
        SUM(
            ROUND(
                (DATEDIFF(
                    LEAST(t.last_day, ? ),
                    GREATEST(t.first_day, ? )
                ) + 1) * t.avg_day_live_room_cost
            , 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            shop_name,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_live_room_cost,
            STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d') AS first_day,
            LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d')) AS last_day
        FROM dd_employee_salary_pubilc_base
        GROUP BY month_str, shop_name
    ) t
    WHERE t.last_day >= ?
      AND t.first_day <= ?
    GROUP BY shop_name

) AS merged
GROUP BY shop_name
    
) AS c ON a.shop_name = c.shop_name

LEFT JOIN
(
    -- ④ 分佣金额
    SELECT 
        t.shop_name,
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary_coop
        WHERE date BETWEEN ? AND ? 
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
    GROUP BY t.shop_name
) AS d ON a.shop_name = d.shop_name

ORDER BY a.yong_hu_zhi_fu_price DESC;
        `

    } else {
        shop_kan_ban_sql = `
        SELECT 
    a.shop_name,
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(IFNULL(b.qian_chuan_cost, 0), 2) AS qian_chuan_cost,
    ROUND(IFNULL(b.gua_tie_cost, 0), 2) AS gua_tie_cost,
    ROUND(IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 0), 2) AS ren_gong_cost,
    ROUND(IFNULL(d.fen_yong_price, 0), 2) AS fen_yong_price,

    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,

    -- ROI
    IF((IFNULL(b.qian_chuan_cost,0) + IFNULL(b.gua_tie_cost,0)) > 0,
       ROUND((a.yong_hu_zhi_fu_price - IFNULL(b.bu_dan_cost,0)) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,

    -- 业务利润
    ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
    IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0)), 2) AS ye_wu_li_run,

    -- 利润率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
       IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0))
       * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        shop_name,
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE month_str BETWEEN ? AND ? 
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS a

LEFT JOIN
(
    -- ② 千川成本
SELECT 
    all_shop.shop_name,
    IFNULL(q.qian_chuan_cost, 0) AS qian_chuan_cost,
    IFNULL(s.bu_dan_cost, 0) AS bu_dan_cost,
    IFNULL(s.gua_tie_cost, 0) AS gua_tie_cost
FROM 
(
    -- 店铺全集（去重）
    SELECT DISTINCT shop_name FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND \`month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_pu_tong_zheng_ti
    WHERE \`year_month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_plan
    WHERE \`year_month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_touhou
    WHERE \`year_month\` BETWEEN ? AND ?
) AS all_shop

LEFT JOIN 
(
    -- 自播：补单费 / 挂贴费
    SELECT 
        shop_name,
        SUM(order_subsidy) AS bu_dan_cost,
        SUM(hanging_fee) AS gua_tie_cost
    FROM dd_self_live_summary
    WHERE \`month\` BETWEEN ? AND ?
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS s
    ON all_shop.shop_name = s.shop_name

LEFT JOIN
(
    -- 千川成本（3表合并）
    SELECT 
        dou_dian AS shop_name,
        SUM(all_cost) AS qian_chuan_cost
    FROM (
        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE \`year_month\` BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_plan
        WHERE \`year_month\` BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_touhou
        WHERE \`year_month\` BETWEEN ? AND ?
    ) AS qc
    GROUP BY dou_dian
) AS q
    ON all_shop.shop_name = q.shop_name

ORDER BY all_shop.shop_name



) AS b ON a.shop_name = b.shop_name

LEFT JOIN
(
    -- ③ 人工成本
    
    SELECT 
    shop_name,
    SUM(cost) AS ren_gong_cost
FROM (

    /* ① 直播人工成本 */
    SELECT 
        shop_name,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ② 兼职人工成本 */
    SELECT 
        shop_name,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ③ 公共工资分摊成本（整月） */
    SELECT 
        shop_name,
        SUM(
            ROUND(avg_day_cost * month_days, 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            shop_name,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_cost,
            DAY(LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d'))) AS month_days
        FROM dd_employee_salary_pubilc_base
        WHERE month_str BETWEEN ? AND ?
        GROUP BY month_str, shop_name
    ) AS t
    GROUP BY shop_name

) AS merged
GROUP BY shop_name
    
) AS c ON a.shop_name = c.shop_name

LEFT JOIN
(
    -- ④ 分佣金额
    SELECT 
        t.shop_name,
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary
        WHERE month_str BETWEEN ? AND ? 
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
    GROUP BY t.shop_name
) AS d ON a.shop_name = d.shop_name

ORDER BY a.yong_hu_zhi_fu_price DESC;
        `

        shop_kan_ban_sql_self = `
        SELECT 
    a.shop_name,
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(IFNULL(b.qian_chuan_cost, 0), 2) AS qian_chuan_cost,
    ROUND(IFNULL(b.gua_tie_cost, 0), 2) AS gua_tie_cost,
    ROUND(IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 0), 2) AS ren_gong_cost,
    ROUND(IFNULL(d.fen_yong_price, 0), 2) AS fen_yong_price,

    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,

    -- ROI
    IF((IFNULL(b.qian_chuan_cost,0) + IFNULL(b.gua_tie_cost,0)) > 0,
       ROUND((a.yong_hu_zhi_fu_price - IFNULL(b.bu_dan_cost,0)) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,

    -- 业务利润
    ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
    IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0)), 2) AS ye_wu_li_run,

    -- 利润率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
       IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0))
       * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        shop_name,
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary_self
    WHERE month_str BETWEEN ? AND ? 
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS a

LEFT JOIN
(
    -- ② 千川成本
SELECT 
    all_shop.shop_name,
    IFNULL(q.qian_chuan_cost, 0) AS qian_chuan_cost,
    IFNULL(s.bu_dan_cost, 0) AS bu_dan_cost,
    IFNULL(s.gua_tie_cost, 0) AS gua_tie_cost
FROM 
(
    -- 店铺全集（去重）
    SELECT DISTINCT shop_name FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND \`month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_pu_tong_zheng_ti
    WHERE \`year_month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_plan
    WHERE \`year_month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_touhou
    WHERE \`year_month\` BETWEEN ? AND ?
) AS all_shop

LEFT JOIN 
(
    -- 自播：补单费 / 挂贴费
    SELECT 
        shop_name,
        SUM(order_subsidy) AS bu_dan_cost,
        SUM(hanging_fee) AS gua_tie_cost
    FROM dd_self_live_summary
    WHERE \`month\` BETWEEN ? AND ?
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS s
    ON all_shop.shop_name = s.shop_name

LEFT JOIN
(
    -- 千川成本（3表合并）
    SELECT 
        dou_dian AS shop_name,
        SUM(all_cost) AS qian_chuan_cost
    FROM (
        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE \`year_month\` BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_plan
        WHERE \`year_month\` BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_touhou
        WHERE \`year_month\` BETWEEN ? AND ?
    ) AS qc
    GROUP BY dou_dian
) AS q
    ON all_shop.shop_name = q.shop_name

ORDER BY all_shop.shop_name

) AS b ON a.shop_name = b.shop_name

LEFT JOIN
(
    -- ③ 人工成本
    
    SELECT 
    shop_name,
    SUM(cost) AS ren_gong_cost
FROM (

    /* ① 直播人工成本 */
    SELECT 
        shop_name,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ② 兼职人工成本 */
    SELECT 
        shop_name,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ③ 公共工资分摊成本（整月） */
    SELECT 
        shop_name,
        SUM(
            ROUND(avg_day_cost * month_days, 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            shop_name,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_cost,
            DAY(LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d'))) AS month_days
        FROM dd_employee_salary_pubilc_base
        WHERE month_str BETWEEN ? AND ?
        GROUP BY month_str, shop_name
    ) AS t
    GROUP BY shop_name

) AS merged
GROUP BY shop_name
    
) AS c ON a.shop_name = c.shop_name

LEFT JOIN
(
    -- ④ 分佣金额
    SELECT 
        t.shop_name,
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary_self
        WHERE month_str BETWEEN ? AND ? 
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
    GROUP BY t.shop_name
) AS d ON a.shop_name = d.shop_name

ORDER BY a.yong_hu_zhi_fu_price DESC;
        `

        shop_kan_ban_sql_coop = `
        SELECT 
    a.shop_name,
    ROUND(a.yong_hu_zhi_fu_price, 2) AS yong_hu_zhi_fu_price,
    ROUND(a.tui_kuan_price, 2) AS tui_kuan_price,
    ROUND(IFNULL(b.qian_chuan_cost, 0), 2) AS qian_chuan_cost,
    ROUND(IFNULL(b.gua_tie_cost, 0), 2) AS gua_tie_cost,
    ROUND(IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011), 0), 2) AS ren_gong_cost,
    ROUND(IFNULL(d.fen_yong_price, 0), 2) AS fen_yong_price,

    -- 签收率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS qian_shou_lv,

    -- ROI
    IF((IFNULL(b.qian_chuan_cost,0) + IFNULL(b.gua_tie_cost,0)) > 0,
       ROUND((a.yong_hu_zhi_fu_price - IFNULL(b.bu_dan_cost,0)) / (b.qian_chuan_cost + b.gua_tie_cost), 2),
       0) AS roi,

    -- 业务利润
    ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
    IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0)), 2) AS ye_wu_li_run,

    -- 利润率
    IF(a.yong_hu_zhi_fu_price > 0,
       ROUND((IFNULL(d.fen_yong_price,0) - IFNULL(b.qian_chuan_cost,0) - IFNULL(b.gua_tie_cost,0) - 
       IFNULL((c.ren_gong_cost + (a.yong_hu_zhi_fu_price - a.tui_kuan_price) * 0.011),0))
       * 100 / a.yong_hu_zhi_fu_price, 2),
       0) AS li_run_lv

FROM
(
    -- ① 交易汇总
    SELECT 
        shop_name,
        SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
        SUM(refund_amount_payment_time + (qianchuan_coupon_amount - refund_qianchuan_coupon_amount)
            + (platform_subsidy_amount - refund_platform_subsidy_amount)) AS tui_kuan_price
    FROM dd_shop_transaction_summary_coop
    WHERE month_str BETWEEN ? AND ? 
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS a

LEFT JOIN
(
    -- ② 千川成本
   SELECT 
    all_shop.shop_name,
    IFNULL(q.qian_chuan_cost, 0) AS qian_chuan_cost,
    IFNULL(s.bu_dan_cost, 0) AS bu_dan_cost,
    IFNULL(s.gua_tie_cost, 0) AS gua_tie_cost
FROM 
(
    -- 店铺全集（去重）
    SELECT DISTINCT shop_name FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND \`month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_pu_tong_zheng_ti
    WHERE \`year_month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_plan
    WHERE \`year_month\` BETWEEN ? AND ?

    UNION

    SELECT DISTINCT dou_dian AS shop_name
    FROM dd_qian_chuan_quanyu_touhou
    WHERE \`year_month\` BETWEEN ? AND ?
) AS all_shop

LEFT JOIN 
(
    -- 自播：补单费 / 挂贴费
    SELECT 
        shop_name,
        SUM(order_subsidy) AS bu_dan_cost,
        SUM(hanging_fee) AS gua_tie_cost
    FROM dd_self_live_summary
    WHERE \`month\` BETWEEN ? AND ?
      AND shop_name IS NOT NULL
    GROUP BY shop_name
) AS s
    ON all_shop.shop_name = s.shop_name

LEFT JOIN
(
    -- 千川成本（3表合并）
    SELECT 
        dou_dian AS shop_name,
        SUM(all_cost) AS qian_chuan_cost
    FROM (
        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_pu_tong_zheng_ti
        WHERE \`year_month\` BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_plan
        WHERE \`year_month\` BETWEEN ? AND ?

        UNION ALL

        SELECT dou_dian, all_cost
        FROM dd_qian_chuan_quanyu_touhou
        WHERE \`year_month\` BETWEEN ? AND ?
    ) AS qc
    GROUP BY dou_dian
) AS q
    ON all_shop.shop_name = q.shop_name

ORDER BY all_shop.shop_name

) AS b ON a.shop_name = b.shop_name

LEFT JOIN
(
    -- ③ 人工成本
    
    SELECT 
    shop_name,
    SUM(cost) AS ren_gong_cost
FROM (

    /* ① 直播人工成本 */
    SELECT 
        shop_name,
        SUM(day_cost) AS cost
    FROM dd_day_live_cheng_ben
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ② 兼职人工成本 */
    SELECT 
        shop_name,
        SUM(salary_day) AS cost
    FROM dd_employee_salary_jian_zhi_base
    WHERE LEFT(date, 7) BETWEEN ? AND ?
    GROUP BY shop_name

    UNION ALL

    /* ③ 公共工资分摊成本（整月） */
    SELECT 
        shop_name,
        SUM(
            ROUND(avg_day_cost * month_days, 2)
        ) AS cost
    FROM (
        SELECT 
            month_str,
            shop_name,
            ROUND(SUM(avg_day_live_room_cost), 2) AS avg_day_cost,
            DAY(LAST_DAY(STR_TO_DATE(CONCAT(month_str, '-01'), '%Y-%m-%d'))) AS month_days
        FROM dd_employee_salary_pubilc_base
        WHERE month_str BETWEEN ? AND ?
        GROUP BY month_str, shop_name
    ) AS t
    GROUP BY shop_name

) AS merged
GROUP BY shop_name
    
) AS c ON a.shop_name = c.shop_name

LEFT JOIN
(
    -- ④ 分佣金额
    SELECT 
        t.shop_name,
        SUM((t.yong_hu_zhi_fu_price - t.tui_kuan_price) * IFNULL(c.commission_rate_avg, 0)) AS fen_yong_price
    FROM (
        SELECT 
            shop_name,
            SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount) AS yong_hu_zhi_fu_price,
            SUM(refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
                + platform_subsidy_amount - refund_platform_subsidy_amount) AS tui_kuan_price
        FROM dd_shop_transaction_summary_coop
        WHERE month_str BETWEEN ? AND ? 
          AND shop_name IS NOT NULL
        GROUP BY shop_name
    ) AS t
    LEFT JOIN (
        SELECT shop_name, AVG(commission_rate) AS commission_rate_avg
        FROM dd_douyin_shop_commission_rate
        GROUP BY shop_name
    ) AS c ON t.shop_name = c.shop_name
    GROUP BY t.shop_name
) AS d ON a.shop_name = d.shop_name

ORDER BY a.yong_hu_zhi_fu_price DESC
        `
    }


    const shop_kan_ban_datas_zhengti = await db.query(shop_kan_ban_sql, sql_params); //店铺 整体成交
    const shop_kan_ban_datas_self = await db.query(shop_kan_ban_sql_self, sql_params); //店铺 自营成交
    const shop_kan_ban_datas_coop = await db.query(shop_kan_ban_sql_coop, sql_params); //店铺  合作成交




    /*整体成交_每个店铺_折线图 sql + datas */
    let shop_zheng_ti_cheng_jiao_line_sql = ``
    let shop_self_cheng_jiao_line_sql = ``
    let shop_coop_cheng_jiao_line_sql = ``
    let min_max_date_sql = ``
    const shop_zheng_ti_cheng_jiao_line_sql_params = [];
    shop_zheng_ti_cheng_jiao_line_sql_params.push(star_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(end_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(star_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(end_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(star_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(end_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(star_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(end_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(star_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(end_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(star_parm);
    shop_zheng_ti_cheng_jiao_line_sql_params.push(end_parm);
    if (date_or_month === "date") {

        shop_zheng_ti_cheng_jiao_line_sql = `
        SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
LEFT JOIN (
    
SELECT qc.date,qc.dou_dian AS shop_name, ROUND(SUM(qc.all_cost), 2) AS qian_chuan_cost
        FROM (
            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE date BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE date BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE date BETWEEN ? AND ? 
        ) AS qc  GROUP BY date,qc.dou_dian

) AS b
ON a.date = b.date AND a.shop_name = b.shop_name
UNION
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
RIGHT JOIN (
    SELECT 
        date,
        shop_name,
        SUM(daily_cost) AS qian_chuan_cost
    FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name
        `

        shop_coop_cheng_jiao_line_sql = `
        SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_coop
    WHERE date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
LEFT JOIN (
SELECT qc.date,qc.dou_dian AS shop_name, ROUND(SUM(qc.all_cost), 2) AS qian_chuan_cost
        FROM (
            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE date BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE date BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE date BETWEEN ? AND ? 
        ) AS qc  GROUP BY date,qc.dou_dian

) AS b
ON a.date = b.date AND a.shop_name = b.shop_name
UNION
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_coop
    WHERE date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
RIGHT JOIN (
    SELECT 
        date,
        shop_name,
        SUM(daily_cost) AS qian_chuan_cost
    FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name
        `

        shop_self_cheng_jiao_line_sql = `
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_self
    WHERE date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
LEFT JOIN (
    SELECT qc.date,qc.dou_dian AS shop_name, ROUND(SUM(qc.all_cost), 2) AS qian_chuan_cost
            FROM (
                SELECT date,dou_dian, all_cost
                FROM dd_qian_chuan_pu_tong_zheng_ti
                WHERE date BETWEEN ? AND ? 
    
                UNION ALL
    
                SELECT date,dou_dian, all_cost
                FROM dd_qian_chuan_quanyu_plan
                WHERE date BETWEEN ? AND ? 
    
                UNION ALL
    
                SELECT date,dou_dian, all_cost
                FROM dd_qian_chuan_quanyu_touhou
                WHERE date BETWEEN ? AND ? 
            ) AS qc  GROUP BY date,qc.dou_dian
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name

UNION

SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_self
    WHERE date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
RIGHT JOIN (
    SELECT 
        date,
        shop_name,
        SUM(daily_cost) AS qian_chuan_cost
    FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND date BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name;
`

        min_max_date_sql = `SELECT MIN(date) AS star_date ,MAX(date) AS end_date
FROM dd_carrier_composition_datas WHERE  date BETWEEN ? AND ?`

    } else {

        shop_zheng_ti_cheng_jiao_line_sql = `
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE month_str BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
LEFT JOIN (
    SELECT qc.date,qc.dou_dian AS shop_name, ROUND(SUM(qc.all_cost), 2) AS qian_chuan_cost
        FROM (
            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE \`year_month\` BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE \`year_month\` BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE \`year_month\` BETWEEN ? AND ? 
        ) AS qc  GROUP BY date,qc.dou_dian
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name

UNION

SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary
    WHERE month_str BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
RIGHT JOIN (
    SELECT 
        date,
        shop_name,
        SUM(daily_cost) AS qian_chuan_cost
    FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND \`month\` BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name;
`

        shop_coop_cheng_jiao_line_sql = `
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_coop
    WHERE month_str BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
LEFT JOIN (
    SELECT qc.date,qc.dou_dian AS shop_name, ROUND(SUM(qc.all_cost), 2) AS qian_chuan_cost
        FROM (
            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE \`year_month\` BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE \`year_month\` BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE \`year_month\` BETWEEN ? AND ? 
        ) AS qc  GROUP BY date,qc.dou_dian
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name

UNION

SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_coop
    WHERE month_str BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
RIGHT JOIN (
    SELECT 
        date,
        shop_name,
        SUM(daily_cost) AS qian_chuan_cost
    FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND \`month\` BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name;
`

        shop_self_cheng_jiao_line_sql = `
SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    IF(b.qian_chuan_cost IS NULL,0,b.qian_chuan_cost) AS qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_self
    WHERE month_str BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
LEFT JOIN (
    SELECT qc.date,qc.dou_dian AS shop_name, ROUND(SUM(qc.all_cost), 2) AS qian_chuan_cost
        FROM (
            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_pu_tong_zheng_ti
            WHERE \`year_month\` BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_plan
            WHERE \`year_month\` BETWEEN ? AND ? 

            UNION ALL

            SELECT date,dou_dian, all_cost
            FROM dd_qian_chuan_quanyu_touhou
            WHERE \`year_month\` BETWEEN ? AND ? 
        ) AS qc  GROUP BY date,qc.dou_dian
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name

UNION

SELECT 
    CAST(COALESCE(a.date, b.date) AS CHAR) AS date_str,
    COALESCE(a.shop_name, b.shop_name) AS shop_name,
    a.yong_hu_zhi_fu_price,
    a.tui_kuan_price,
    b.qian_chuan_cost
FROM (
    SELECT 
        date,
        shop_name,
        ROUND(SUM(user_payment_amount + qianchuan_coupon_amount + platform_subsidy_amount), 2) AS yong_hu_zhi_fu_price,
        ROUND(SUM(
            refund_amount_payment_time + qianchuan_coupon_amount - refund_qianchuan_coupon_amount
            + platform_subsidy_amount - refund_platform_subsidy_amount
        ), 2) AS tui_kuan_price
    FROM dd_shop_transaction_summary_self
    WHERE month_str BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS a
RIGHT JOIN (
    SELECT 
        date,
        shop_name,
        SUM(daily_cost) AS qian_chuan_cost
    FROM dd_self_live_summary
    WHERE shop_name IS NOT NULL
      AND \`month\` BETWEEN ? AND ?
    GROUP BY date, shop_name
) AS b
ON a.date = b.date AND a.shop_name = b.shop_name;
`

        min_max_date_sql = `SELECT MIN(date) AS star_date ,MAX(date) AS end_date
FROM dd_carrier_composition_datas WHERE  month_str BETWEEN ? AND ? `

    }
    const shop_line_datas_zheng_ti = await db.query(shop_zheng_ti_cheng_jiao_line_sql, shop_zheng_ti_cheng_jiao_line_sql_params);
    const shop_line_datas_self = await db.query(shop_self_cheng_jiao_line_sql, shop_zheng_ti_cheng_jiao_line_sql_params);
    const shop_line_datas_coop = await db.query(shop_coop_cheng_jiao_line_sql, shop_zheng_ti_cheng_jiao_line_sql_params);


    let shop_datas = {}
    // KPI 模板定义
    const kpiTemplate = {
        "yong_hu_zhi_fu_price": {label: '支付金额', value: '0'},
        "tui_kuan_price": {label: '退款金额', value: '0'},
        "qian_shou_lv": {label: '签收率', value: '0'},
        "qian_chuan_cost": {label: '千川消耗', value: '0'},
        "roi": {label: 'ROI', value: '0'},
        "ren_gong_cost": {label: '人工成本', value: '0'},
        "ye_wu_li_run": {label: '业务利润', value: '0'},
        "li_run_lv": {label: '利润率', value: '0'}
    }
    //kpi顺序
    const kpiNameShunXu = ["yong_hu_zhi_fu_price", "tui_kuan_price", "qian_shou_lv", "qian_chuan_cost"
        , "roi", "ren_gong_cost", "ye_wu_li_run", "li_run_lv"]
    if (shop_kan_ban_datas_zhengti.length === 0) {

    } else {
        //整体成交店铺kpi
        for (const item of shop_kan_ban_datas_zhengti) {
            const shop_name = item.shop_name
            shop_datas[shop_name] = {}
            shop_datas[shop_name]["kpiData"] = {}
            shop_datas[shop_name]["kpiData"]["all"] = []          //整体
            shop_datas[shop_name]["kpiData"]["self"] = []         //自营
            shop_datas[shop_name]["kpiData"]["coop"] = []         //合作

            const newKpi_all = JSON.parse(JSON.stringify(kpiTemplate))
            const newKpi_self = JSON.parse(JSON.stringify(kpiTemplate))
            const newKpi_coop = JSON.parse(JSON.stringify(kpiTemplate))

            for (const key of kpiNameShunXu) {

                let value_res = '0'
                let v_all = newKpi_all[key]

                let v_self = newKpi_self[key]
                let v_coop = newKpi_coop[key]

                if (key === "li_run_lv" || key === "qian_shou_lv") {
                    value_res = `${item[key]}%`
                    v_all["value"] = value_res;
                } else if (key === "gua_tie_cost" || key === "fen_yong_price") {
                    //挂铁和分佣没有 zong_kan_ban_datas_json 没有key
                } else {
                    value_res = await formatNumber(item[key])
                    v_all["value"] = value_res
                }
                shop_datas[shop_name]["kpiData"]["all"].push(v_all);
                shop_datas[shop_name]["kpiData"]["self"].push(v_self);
                shop_datas[shop_name]["kpiData"]["coop"].push(v_coop);
            }

        }

        //自营成交店铺kpi
        for (const item of shop_kan_ban_datas_self) {
            const shop_name = item.shop_name
            shop_datas[shop_name]["kpiData"]["self"] = []         //自营
            const kpi_self = JSON.parse(JSON.stringify(kpiTemplate))

            for (const key of kpiNameShunXu) {

                let value_res = '0'
                let v_self = kpi_self[key]
                if (key === "li_run_lv" || key === "qian_shou_lv") {
                    value_res = `${item[key]}%`
                    v_self["value"] = value_res;
                } else if (key === "gua_tie_cost" || key === "fen_yong_price") {
                    //挂铁和分佣没有 zong_kan_ban_datas_json 没有key
                } else {
                    value_res = await formatNumber(item[key])
                    v_self["value"] = value_res
                }
                shop_datas[shop_name]["kpiData"]["self"].push(v_self);
            }

        }

        //合作成交店铺kpi
        for (const item of shop_kan_ban_datas_coop) {
            const shop_name = item.shop_name
            shop_datas[shop_name]["kpiData"]["coop"] = []         //自营
            const kpi_coop = JSON.parse(JSON.stringify(kpiTemplate))

            for (const key of kpiNameShunXu) {

                let value_res = '0'
                let v_coop = kpi_coop[key]

                // if (key === "li_run_lv" || key === "qian_shou_lv") {
                //     value_res = `${item[key]}%`
                //     v_coop["value"] = value_res;
                // } else if (key === "gua_tie_cost" || key === "fen_yong_price") {
                //     //挂铁和分佣没有 zong_kan_ban_datas_json 没有key
                // } else {
                //     value_res = await formatNumber(item[key])
                //     v_coop["value"] = value_res
                // }
                ////"yong_hu_zhi_fu_price", "tui_kuan_price", "qian_shou_lv"
                if (key === "yong_hu_zhi_fu_price" || key === "tui_kuan_price") {
                    value_res = await formatNumber(item[key])
                    v_coop["value"] = value_res
                } else if (key === "qian_shou_lv") {
                    value_res = `${item[key]}%`
                    v_coop["value"] = value_res;
                }
                shop_datas[shop_name]["kpiData"]["coop"].push(v_coop);
            }

        }

        //店铺 折线图
        //店铺 整体成交
        const shop_line_datas_all_json = {}
        for (const item of shop_line_datas_zheng_ti) {
            const date_str = item["date_str"]
            const shop_name = item["shop_name"]
            if (!(date_str in shop_line_datas_all_json)) {
                shop_line_datas_all_json[date_str] = {}
            }
            if (!(shop_name in shop_line_datas_all_json[date_str])) {
                shop_line_datas_all_json[date_str][shop_name] = {}
            }
            shop_line_datas_all_json[date_str][shop_name] = item
        }

        //店铺 自营成交
        const shop_line_datas_self_json = {}
        for (const item of shop_line_datas_self) {
            const date_str = item["date_str"]
            const shop_name = item["shop_name"]
            if (!(date_str in shop_line_datas_self_json)) {
                shop_line_datas_self_json[date_str] = {}
            }
            if (!(shop_name in shop_line_datas_self_json[date_str])) {
                shop_line_datas_self_json[date_str][shop_name] = {}
            }
            shop_line_datas_self_json[date_str][shop_name] = item
        }

        //店铺 合作成交
        const shop_line_datas_coop_json = {}
        for (const item of shop_line_datas_coop) {
            const date_str = item["date_str"]
            const shop_name = item["shop_name"]
            if (!(date_str in shop_line_datas_coop_json)) {
                shop_line_datas_coop_json[date_str] = {}
            }
            if (!(shop_name in shop_line_datas_coop_json[date_str])) {
                shop_line_datas_coop_json[date_str][shop_name] = {}
            }
            shop_line_datas_coop_json[date_str][shop_name] = item
        }

        for (const shop_name in shop_datas) {
            shop_datas[shop_name]["trendData"] = {}
            shop_datas[shop_name]["trendData"]["all"] = {}
            shop_datas[shop_name]["trendData"]["self"] = {}
            shop_datas[shop_name]["trendData"]["coop"] = {}

            //自营 和 合作 详情
            shop_datas[shop_name]["selfCoopDetails"] = []

            //店铺 整体成交 折线图
            shop_datas[shop_name]["trendData"]["all"]["days"] = []
            shop_datas[shop_name]["trendData"]["all"]["pay"] = []
            shop_datas[shop_name]["trendData"]["all"]["refund"] = []
            shop_datas[shop_name]["trendData"]["all"]["discount"] = []
            //店铺 自营成交 折线图
            shop_datas[shop_name]["trendData"]["self"]["days"] = []
            shop_datas[shop_name]["trendData"]["self"]["pay"] = []
            shop_datas[shop_name]["trendData"]["self"]["refund"] = []
            shop_datas[shop_name]["trendData"]["self"]["discount"] = []
            //店铺 合作成交 折线图
            shop_datas[shop_name]["trendData"]["coop"]["days"] = []
            shop_datas[shop_name]["trendData"]["coop"]["pay"] = []
            shop_datas[shop_name]["trendData"]["coop"]["refund"] = []
            shop_datas[shop_name]["trendData"]["coop"]["discount"] = []

            for (const day of allDates) {
                shop_datas[shop_name]["trendData"]["all"]["days"].push(day);
                shop_datas[shop_name]["trendData"]["self"]["days"].push(day);
                shop_datas[shop_name]["trendData"]["coop"]["days"].push(day);

                if (day in shop_line_datas_all_json) {

                    if (shop_name in shop_line_datas_all_json[day]) {
                        const pay = shop_line_datas_all_json[day][shop_name]["yong_hu_zhi_fu_price"]
                        if (pay == null) {
                            shop_datas[shop_name]["trendData"]["all"]["pay"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["all"]["pay"].push(pay);
                        }
                        const refund = shop_line_datas_all_json[day][shop_name]["tui_kuan_price"]
                        if (refund == null) {
                            shop_datas[shop_name]["trendData"]["all"]["refund"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["all"]["refund"].push(refund);
                        }

                        const discount = shop_line_datas_all_json[day][shop_name]["qian_chuan_cost"]
                        if (discount == null) {
                            shop_datas[shop_name]["trendData"]["all"]["discount"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["all"]["discount"].push(discount);
                        }

                    } else {
                        shop_datas[shop_name]["trendData"]["all"]["pay"].push("0");
                        shop_datas[shop_name]["trendData"]["all"]["refund"].push("0");
                        shop_datas[shop_name]["trendData"]["all"]["discount"].push("0");
                    }


                } else {
                    shop_datas[shop_name]["trendData"]["all"]["pay"].push("0");
                    shop_datas[shop_name]["trendData"]["all"]["refund"].push("0");
                    shop_datas[shop_name]["trendData"]["all"]["discount"].push("0");
                }


                if (day in shop_line_datas_self_json) {

                    if (shop_name in shop_line_datas_self_json[day]) {


                        const pay = shop_line_datas_self_json[day][shop_name]["yong_hu_zhi_fu_price"]
                        if (pay == null) {
                            shop_datas[shop_name]["trendData"]["self"]["pay"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["self"]["pay"].push(pay);
                        }

                        const refund = shop_line_datas_self_json[day][shop_name]["tui_kuan_price"]
                        if (refund == null) {
                            shop_datas[shop_name]["trendData"]["self"]["refund"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["self"]["refund"].push(refund);
                        }
                        const discount = shop_line_datas_self_json[day][shop_name]["qian_chuan_cost"];
                        if (discount == null) {
                            shop_datas[shop_name]["trendData"]["self"]["discount"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["self"]["discount"].push(discount);
                        }

                    } else {
                        shop_datas[shop_name]["trendData"]["self"]["pay"].push("0");
                        shop_datas[shop_name]["trendData"]["self"]["refund"].push("0");
                        shop_datas[shop_name]["trendData"]["self"]["discount"].push("0");
                    }

                } else {
                    shop_datas[shop_name]["trendData"]["self"]["pay"].push("0");
                    shop_datas[shop_name]["trendData"]["self"]["refund"].push("0");
                    shop_datas[shop_name]["trendData"]["self"]["discount"].push("0");
                }


                if (day in shop_line_datas_coop_json) {


                    if (shop_name in shop_line_datas_coop_json[day]) {
                        const pay = shop_line_datas_coop_json[day][shop_name]["yong_hu_zhi_fu_price"]
                        if (pay == null) {
                            shop_datas[shop_name]["trendData"]["coop"]["pay"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["coop"]["pay"].push(pay);
                        }

                        const refund = shop_line_datas_coop_json[day][shop_name]["tui_kuan_price"]
                        if (refund == null) {
                            shop_datas[shop_name]["trendData"]["coop"]["refund"].push("0");
                        } else {
                            shop_datas[shop_name]["trendData"]["coop"]["refund"].push(refund);
                        }
                        // let discount = shop_line_datas_coop_json[day][shop_name]["qian_chuan_cost"]
                        const discount = 0;
                        shop_datas[shop_name]["trendData"]["coop"]["discount"].push(discount);
                    } else {
                        shop_datas[shop_name]["trendData"]["coop"]["pay"].push("0");
                        shop_datas[shop_name]["trendData"]["coop"]["refund"].push("0");
                        shop_datas[shop_name]["trendData"]["coop"]["discount"].push("0");
                    }


                } else {
                    shop_datas[shop_name]["trendData"]["coop"]["pay"].push("0");
                    shop_datas[shop_name]["trendData"]["coop"]["refund"].push("0");
                    shop_datas[shop_name]["trendData"]["coop"]["discount"].push("0");
                }

            }

        }


        const min_max_date_datas = await db.query(min_max_date_sql, sql_params_star_end_parm);
        if (min_max_date_datas.length > 0) {
            const star_date = min_max_date_datas[0]["star_date"];
            const end_date = min_max_date_datas[0]["end_date"];
            //上个周期的起始结束日期
            const {start_pre, end_pre} = await getPreviousPeriod(star_date, end_date);


            const carrier_composition_group_sql_param = [];
            carrier_composition_group_sql_param.push(star_date);
            carrier_composition_group_sql_param.push(end_date);
            const carrier_composition_group_pre_sql_param = [];
            carrier_composition_group_pre_sql_param.push(start_pre);
            carrier_composition_group_pre_sql_param.push(end_pre);

            const carrier_composition_group_sql = `SELECT shop_name,is_self,type,ROUND(SUM(user_payment_amount),2) AS  total_account
FROM dd_carrier_composition_datas WHERE  date BETWEEN ? AND ?  GROUP BY shop_name,is_self,type;`

            const carrier_composition_group_pre_sql = `SELECT shop_name,is_self,type,ROUND(SUM(user_payment_amount),2) AS  total_account
FROM dd_carrier_composition_datas WHERE  date BETWEEN ? AND ?  GROUP BY shop_name,is_self,type;`

            //所选周期的数据
            const carrier_composition_group_datas = await db.query(carrier_composition_group_sql, carrier_composition_group_sql_param);
            //上个周期的数据
            const carrier_composition_group_pre_datas = await db.query(carrier_composition_group_pre_sql, carrier_composition_group_pre_sql_param);


            //店铺自营数据
            let shop_self_detals = {}
            let shop_self_detals_pre = {}
            //上个周期店铺自营数据
            let shop_coop_detals = {}
            let shop_coop_detals_pre = {}
            const types = ["直播", "短视频", "商品卡", "图文", "其他"]

            for (const shop_name in shop_datas) {

                //自营
                shop_self_detals[shop_name] = {}
                shop_self_detals[shop_name]["title"] = "自营-用户支付金额";
                shop_self_detals[shop_name]["total"] = 0   // 自营 总金额
                shop_self_detals[shop_name]["items"] = []  // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的数组数据
                shop_self_detals[shop_name]["type_account"] = {}   // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的json数据
                //自营上周期
                shop_self_detals_pre[shop_name] = {}
                shop_self_detals_pre[shop_name]["title"] = "自营-用户支付金额";
                shop_self_detals_pre[shop_name]["total"] = 0   // 自营 总金额
                shop_self_detals_pre[shop_name]["items"] = []
                shop_self_detals_pre[shop_name]["type_account"] = {}
                //合作
                shop_coop_detals[shop_name] = {}
                shop_coop_detals[shop_name]["title"] = "合作-用户支付金额";
                shop_coop_detals[shop_name]["total"] = 0   // 自营 总金额
                shop_coop_detals[shop_name]["items"] = []
                shop_coop_detals[shop_name]["type_account"] = {}
                //合作上周期
                shop_coop_detals_pre[shop_name] = {}
                shop_coop_detals_pre[shop_name]["title"] = "合作-用户支付金额";
                shop_coop_detals_pre[shop_name]["total"] = 0   // 自营 总金额
                shop_coop_detals_pre[shop_name]["items"] = []
                shop_coop_detals_pre[shop_name]["type_account"] = {}

                //"直播", "短视频", "商品卡", "图文","其他"
                for (const type of types) {
                    shop_self_detals[shop_name]["type_account"][type] = {
                        name: type,
                        value: 0,
                        growth: 0,
                        visible: true,
                        "shop_name": shop_name
                    }
                    shop_self_detals_pre[shop_name]["type_account"][type] = {
                        name: type,
                        value: 0,
                        growth: 0,
                        visible: true,
                        "shop_name": shop_name
                    }
                    shop_coop_detals[shop_name]["type_account"][type] = {
                        name: type,
                        value: 0,
                        growth: 0,
                        visible: true,
                        "shop_name": shop_name
                    }
                    shop_coop_detals_pre[shop_name]["type_account"][type] = {
                        name: type,
                        value: 0,
                        growth: 0,
                        visible: true,
                        "shop_name": shop_name
                    }
                }

            }

            for (const item of carrier_composition_group_datas) {
                const shop_name = item["shop_name"]
                const is_self = item["is_self"] // 自营 带货
                let total_account = item["total_account"]; //总金额
                const type = item["type"] //载体类型


                if (is_self === "自营") {
                    if (total_account != null) {
                        total_account = parseFloat(total_account);
                        let value_account = shop_self_detals[shop_name]["type_account"][type]["value"]
                        let shop_self_total_account = shop_self_detals[shop_name]["total"]
                        value_account += total_account;  // 对应 载体的金额 （"直播", "短视频", "商品卡", "图文","其他"）
                        shop_self_total_account += total_account; //店铺 自营总金额
                        shop_self_detals[shop_name]["type_account"][type]["value"] = value_account;
                        shop_self_detals[shop_name]["total"] = shop_self_total_account;
                    }
                } else {
                    //合作
                    if (total_account != null) {
                        total_account = parseFloat(total_account);
                        let value_account = shop_coop_detals[shop_name]["type_account"][type]["value"]
                        let shop_coop_total_account = shop_coop_detals[shop_name]["total"]
                        value_account += total_account;  // 对应 载体的金额 （"直播", "短视频", "商品卡", "图文","其他"）
                        shop_coop_total_account += total_account;  //店铺 合作总金额
                        shop_coop_detals[shop_name]["type_account"][type]["value"] = value_account;
                        shop_coop_detals[shop_name]["total"] = shop_coop_total_account;
                    }
                }
            }


            //上个周期 数据
            for (const item of carrier_composition_group_pre_datas) {
                const shop_name = item["shop_name"]
                const is_self = item["is_self"] // 自营 带货
                let total_account = item["total_account"]; //总金额
                const type = item["type"] //载体类型


                if (is_self === "自营") {
                    if (total_account != null) {
                        total_account = parseFloat(total_account);
                        let value_account = shop_self_detals_pre[shop_name]["type_account"][type]["value"]
                        let shop_self_total_account_pre = shop_self_detals_pre[shop_name]["total"]
                        value_account += total_account;   // 上周期 对应 载体的金额 （"直播", "短视频", "商品卡", "图文","其他"）
                        shop_self_total_account_pre += total_account;  //上周期 店铺 自营总金额
                        shop_self_detals_pre[shop_name]["type_account"][type]["value"] = value_account;
                        shop_self_detals_pre[shop_name]["total"] = shop_self_total_account_pre;
                    }
                } else {
                    //合作
                    if (total_account != null) {
                        total_account = parseFloat(total_account);
                        let value_account = shop_coop_detals_pre[shop_name]["type_account"][type]["value"]
                        let shop_coop_total_account_pre = shop_coop_detals_pre[shop_name]["total"]
                        value_account += total_account; // 上周期 对应 载体的金额 （"直播", "短视频", "商品卡", "图文","其他"）
                        shop_coop_total_account_pre += total_account;  //上周期 店铺 合作总金额
                        shop_coop_detals_pre[shop_name]["type_account"][type]["value"] = value_account;
                        shop_coop_detals_pre[shop_name]["total"] = shop_coop_total_account_pre;
                    }
                }
            }


            //计算 对比上周期 增长比例
            for (const shop_name in shop_datas) {
                //自营
                shop_self_detals[shop_name]["total"] = await formatNumber(shop_self_detals[shop_name]["total"]) // 自营 总金额
                shop_self_detals[shop_name]["items"] = []  // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的数组数据
                let self_type_account_json = shop_self_detals[shop_name]["type_account"]   // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的json数据

                //上周期 自营
                let self_type_account_json_pre = shop_self_detals_pre[shop_name]["type_account"] // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的json数据
                for (let type in self_type_account_json) {

                    //自营 计算 比较上周期 增长比例
                    let v = self_type_account_json[type]
                    let v_pre = self_type_account_json_pre[type]
                    let growth = 0;
                    if (v_pre["value"] > 0) {
                        growth = await roundTo2((v["value"] - v_pre["value"]) * 100 / v_pre["value"]);
                    } else if (v_pre["value"] === 0 && v["value"] === 0) {
                        growth = 0;
                    } else {
                        growth = 100;
                    }
                    v["growth"] = growth;
                    // v["value"] = await formatNumber(v["value"])
                    //合作 计算 比较上周期 增长比例

                }


                //合作
                shop_coop_detals[shop_name]["total"] = await formatNumber(shop_coop_detals[shop_name]["total"]) // 自营 总金额
                shop_coop_detals[shop_name]["items"] = []  // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的数组数据
                let coop_type_account_json = shop_coop_detals[shop_name]["type_account"]   // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的json数据
                //上周期 自营
                let coop_type_account_json_pre = shop_coop_detals_pre[shop_name]["type_account"] // "直播", "短视频", "商品卡", "图文", "其他" 数组对应的json数据
                for (let type in coop_type_account_json) {

                    //自营 计算 比较上周期 增长比例
                    let v_coop = coop_type_account_json[type]
                    let v_coop_pre = coop_type_account_json_pre[type]
                    let growth_coop = 0;
                    if (v_coop_pre["value"] > 0) {
                        growth_coop = await roundTo2((v_coop["value"] - v_coop_pre["value"]) * 100 / v_coop_pre["value"]);
                    } else if (v_coop_pre["value"] === 0 && v_coop_pre["value"] === 0) {
                        growth_coop = 0;
                    } else {
                        growth_coop = 100;
                    }

                    v_coop["growth"] = growth_coop;
                    coop_type_account_json[type] = v_coop
                    // v_coop["value"] = await formatNumber(v_coop["value"])
                    //合作 计算 比较上周期 增长比例

                }


                for (const type of types) {

                    let self_items = shop_self_detals[shop_name]["items"];
                    let coop_items = shop_coop_detals[shop_name]["items"];
                    self_items.push(self_type_account_json[type])
                    coop_items.push(coop_type_account_json[type])

                    shop_self_detals[shop_name]["items"] = self_items
                    shop_coop_detals[shop_name]["items"] = coop_items
                }

                shop_datas[shop_name]["selfCoopDetails"].push(shop_self_detals[shop_name]);
                shop_datas[shop_name]["selfCoopDetails"].push(shop_coop_detals[shop_name]);
            }


        }


    }


    return shop_datas;

}
